﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using DevExpress.XtraEditors.Controls;
namespace KhachSan.Process.Public
{
    class XLDuLieu
    {
        /*
              true đang kích hoạt
			   false chưa kích hoạt
			 
         */
        public static ThongTinDangNhap thongTinDangNhap;
       
        public static string chuoi_ket_noi = ConfigurationManager.ConnectionStrings["chuoi_ket_noi"].ConnectionString;
        public static SqlConnection conn = new SqlConnection(chuoi_ket_noi);
        /// <summary>
        /// Thực thi lệnh sql  không trả về giá trị
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public static string ExecuteNonQuery(string sqlStatement)
        {
            string Error = "";
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStatement, conn);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return Error;
        }
        /// <summary>
        /// Thực thi lệnh sql trả về một giá trị kiểu chuổi
        /// messages là giá trị trả về
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public static string ExecuteScalarString(string sqlStatement, out string messages)
        {
            string Error = "";
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStatement, conn);
                messages = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            messages = "";
            return Error;
        }
        /// <summary>
        /// Load dữ liệu từ store procedue vào  datatable
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public static string LoadDataTable(string sqlStatement, DataTable ketQua)
        {
            string Error = "";
            try
            {
                DataTable bang = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, conn);
                adapter.Fill(bang);
                ketQua = bang;
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            finally
            {
            }

            return Error;
        }
        /// <summary>
        /// Load dữ liệu từ store procedue vào  datatable
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public static DataTable LoadDataTable(string sqlStatement)
        {
            try
            {
                DataTable bang = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlStatement, conn);
                adapter.Fill(bang);
                return bang;
            }
            catch
            {

            }
            return null;
        }
        /// <summary>
        /// Hàm thực thi một lệnh trả về một số
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <param name="messages"></param>
        /// <returns></returns>
        public static string ExecuteScalarNumber(string sqlStatement, out int messages)
        {
            string Error = "";
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStatement, conn);
                messages = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            messages = -1;
            return Error;
        }
        /// <summary>
        /// Thực thi một sp trong sql
        /// sql tên store procedure
        /// prm:danh sách parameter của store procedure
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="prm"></param>
        /// <returns></returns>
        public static bool ExecuteSP(string sql, List<SqlParameter> prm)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in prm)
                {
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        /// <summary>
        /// Load combobox data
        /// sqlStatement: lệnh sql để lấy dữ liệu vào datasource
        /// text: hiển thị
        /// value: Giá trị thực sự
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <param name="text"></param>
        /// <param name="value"></param>
        /// <param name="luoi"></param>
        /// <returns></returns>
        //
        public static string LoadCombobox(string sqlStatement, string text, string value, ComboBox luoi)
        {
            string loi = "";
            try
            {
                DataTable bang = XLDuLieu.LoadDataTable(sqlStatement);
               
                luoi.DataSource = bang;
                luoi.DisplayMember = text;
                luoi.ValueMember = value;
                
            }
            catch (Exception ex)
            {
                loi = ex.ToString();
            }
            return loi;
        }
        /// <summary>
        /// Loai LookupEdit data
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <param name="text"></param>
        /// <param name="value"></param>
        /// <param name="cbb"></param>
        /// <returns></returns>
        public static string LoadCombobox(string sqlStatement, string text, string value, DevExpress.XtraEditors.LookUpEdit cbb)
        {
            string loi = "";
            try
            {
                DataTable bang = XLDuLieu.LoadDataTable(sqlStatement);
                cbb.Properties.DataSource = bang;
                cbb.Properties.DisplayMember = text;
                cbb.Properties.ValueMember = value;
            }
            catch (Exception ex)
            {
                loi = ex.ToString();
            }
            return loi;
        }
    }
}
